package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hngm.bean.Newssheet;
import com.hngm.bean.Newstype;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

public class NewstypeDao {
    public ArrayList<Newstype> queryNewstypeList(){
    	//定义新闻类型集合
    	ArrayList<Newstype> atList = new ArrayList<Newstype>();
    	
    	//定义查询的sql语句
 	   String sql = "select * from newstype";
 	   //调用DBManager
 	   QueryDataResult qdr = DBManager.executeQuery(sql);
 	   //在qdr中获取resultSet结果集
 	   ResultSet rs = qdr.getRs();
 	   //遍历rs结果集
 	   try {
		while(rs.next()){
			   int id = rs.getInt("id");
			   String name = rs.getString("name");
			   Newstype at = new Newstype(id, name);
			   atList.add(at);
		   }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally{
		DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
	}
    	
    	return atList;
    }

	public static int insertNewstype(Newstype p) {
		String sql = String.format("insert into newstype(name) values ('%s')", p.getName());
		return DBManager.executeUpdate(sql);
	}

	public ArrayList<Newstype> querynewstypeByParam(String name, String del) {
		 ArrayList<Newstype> nList = new  ArrayList<Newstype>();
		// 定义sql语句
				// 将传过来的查询条件作为where条件：拼接到sql语句中
				StringBuffer sb = new StringBuffer("select * from newstype where 1=1");

				// 拼接查询条件
				if (StringUtil.isNotNullorEmpty(name)) {
					sb.append(String.format(" and name like '%s'", "%" + name + "%"));
				}
				if (StringUtil.isNotNullorEmpty(del)) {
					sb.append(String.format(" and del = '%s'", del));
				}
				
				// 调用DBManager
				QueryDataResult qdr = DBManager.executeQuery(sb.toString());
				// 在qdr中获取resultSet结果集
				ResultSet rs = qdr.getRs();

				try {
					// 遍历rs结果集
					while (rs.next()) {
						long id = rs.getLong("id");
						String name1 = rs.getString("name");
						long pid = rs.getLong("pid");
						int sort = rs.getInt("sort");
						String del1 = rs.getString("del");
						String creationtime = rs.getString("creationtime");
						String updatetime = rs.getString("updatetime");
						Newstype n = new Newstype(id, name1, pid, sort, del1, creationtime, updatetime);
						
						nList.add(n);
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
				}
		return nList;
	}

	public int querynewstypeByParamCount(String name, String del) {
		// 定义sql语句
				StringBuffer sb = new StringBuffer("select count(1) as count  from newstype where 1=1");
				// 调用DBManager
				QueryDataResult qdr = DBManager.executeQuery(sb.toString());
				// 在qdr中获取resultSet结果集
				ResultSet rs = qdr.getRs();

				try {
					// 遍历rs结果集
					while (rs.next()) {
						return rs.getInt("count");
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
				}
		return 0;
	}

	public int delnewstype(String newstypeId) {
		//定义删除产品的sql语句
		String sql = "delete from newstype where id = " + newstypeId;
		return DBManager.executeUpdate(sql);
	}

	public static int editNewstype(Newstype p) {
		String sql = String.format("update newstype set name='%s' where id = %d", p.getName(),p.getId());
		return DBManager.executeUpdate(sql);
	}
    
}
